package com.offcn.categroytop.dao;

import com.offcn.bean.CategroyTop;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author: 平川
 * @Description:
 * @Date Created in 2021-10-15 0:04
 * @Modified By:
 */
public interface CategroyTopDao {
    //统计分类数量
    @Select("select count(*) from categroymovietable")
    int countCategroy();
    //分类汇总：不同类型电影数量、平均评分、占比
    @Select("SELECT c.title,COUNT(c.title) num,ROUND(AVG(m.score),2) scoreavg,\n" +
            "ROUND(COUNT(c.title)/(SELECT COUNT(moviename) FROM movietable)*100,2) persent\n" +
            "FROM categroymovietable c\n" +
            "JOIN moviedetailtable md\n" +
            "ON FIND_IN_SET(c.title,md.categroy)\n" +
            "JOIN movietable m\n" +
            "ON md.id = m.id\n" +
            "GROUP BY c.title\n" +
            "ORDER BY num DESC\n" +
            "LIMIT 10")
    List<CategroyTop> selectTop();


}
